use ATE
go 


/*
IF OBJECT_ID ( 'TwoDimensionSN', 'U' ) IS NOT NULL 
    DROP table TwoDimensionSN;
GO
CREATE table TwoDimensionSN
(
ProductSN nvarchar(50),
TestTime datetime,
TDSN nvarchar(2000)
)
--*/



IF OBJECT_ID ( 'TwoDimensionSN_selectbyLastSN', 'P' ) IS NOT NULL 
    DROP PROCEDURE TwoDimensionSN_selectbyLastSN;
GO
CREATE PROCEDURE TwoDimensionSN_selectbyLastSN
	@ProductSN nvarchar(50),
	@TDSN nvarchar(2000) output
AS    
    SET NOCOUNT ON; 
    select top 1 @TDSN=td.TDSN 
	from TwoDimensionSN td inner join PackageTest pt 
	on td.ProductSN=pt.ProductSN 
	where pt.PackageProductSN=@ProductSN or pt.ProductSN=@ProductSN 
	order by td.TestTime desc; 	
GO 



IF OBJECT_ID ( 'TwoDimensionSN_selectbySN', 'P' ) IS NOT NULL 
    DROP PROCEDURE TwoDimensionSN_selectbySN;
GO
CREATE PROCEDURE TwoDimensionSN_selectbySN
	@ProductSN nvarchar(50)
AS    
    SET NOCOUNT ON;
    select t.ProductSN,t.TestTime,t.ProductTypeID,t.UserID,t.ENAID,t.IsPass,t.IsSwitch,t.SpecVersion,td.TDSN
	from TwoDimensionSN td inner join PackageTest pt 
	on td.ProductSN=pt.ProductSN 
	inner join Test t 
	on t.ProductSN=td.ProductSN and t.TestTime=td.TestTime 
	where pt.PackageProductSN=@ProductSN or pt.ProductSN=@ProductSN 
	order by TestTime desc ;	
GO









exec TwoDimensionSN_selectbyLastSN 'ZM154370085',''
exec TwoDimensionSN_selectbySN 'ZM154370085'

select top 10 * from TwoDimensionSN order by TestTime desc

select top 10 * from PackageTest order by PackageTime desc

select top 10 * from Test where ProductSN='37T3190500304F300259' order by TestTime desc

insert into TwoDimensionSN 
select '37T3190500304F300259','2015-10-26 22:16:11.000','15Z2080E9047:0X00000056'








